package com.nuoniu.sibanyun.mapper.provider;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.nuoniu.sibanyun.common.lang.StringUtils;
import com.nuoniu.sibanyun.entity.crm.*;
import com.nuoniu.sibanyun.entity.dto.CrmFollowAnalysis;
import com.nuoniu.sibanyun.userThread.UserInfo;
import org.apache.ibatis.jdbc.SQL;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.formula.functions.WeekdayFunc;

import java.util.Map;

/**
 * @auther 小啊刚
 * @date 2020/6/3
 */
public class CrmMapperProvider {

    /**对应数据库表名**/
    private String TABLE_NAME = "crm_consignee a";

    /**
     * 获取发货人列表
     * @author 小啊刚
     * @date 2020/6/3
     */
    public String getConsigneeList(CrmConsignee consignee, UserInfo user) {
        SQL sql = new SQL();
        sql.SELECT("a.*");
        sql.FROM("crm_consignee a");
        sql.INNER_JOIN("pub_customer crm on a.crm_id = crm.id");
        sql.WHERE("a.status = 0");
        if(consignee.getCrmId() != null) {
            sql.WHERE("a.crm_id = #{consignee.crmId}");
        }else {
            sql.WHERE("crm.crm_type = 2");
        }
        if(StringUtils.isNotBlank(consignee.getCrmName())) {
            sql.WHERE("a.crm_name like \"%\"#{consignee.crmName}\"%\"");
        }
        if(StringUtils.isNotBlank(consignee.getConsignee())) {
            sql.WHERE("a.consignee like \"%\"#{consignee.consignee}\"%\"");
        }
        if(StringUtils.isNotBlank(consignee.getConsigneeAddress())) {
            sql.WHERE("a.consignee_address like \"%\"#{consignee.consigneeAddress}\"%\"");
        }
        if(StringUtils.isNotBlank(consignee.getMobile())) {
            sql.WHERE("a.mobile = #{consignee.mobile}");
        }
        if(StringUtils.isNotBlank(consignee.getPhone())) {
            sql.WHERE("a.phone = #{consignee.phone}");
        }
        return sql.toString();
    }

    /**
     * 获取财务信息列表
     * @author 小啊刚
     * @date 2020/7/7
     */
    public String getFinanceInfoList(CrmFinanceInfo finance, UserInfo user) {
        SQL sql = new SQL();
        sql.SELECT("a.*, crm.crm_name crmName");
        sql.FROM("crm_finance_info a");
        sql.INNER_JOIN("pub_customer crm on a.crm_id = crm.id");
        sql.WHERE("a.status = 0");
        if(finance.getCrmId() != null) {
            sql.WHERE("a.crm_id = #{finance.crmId}");
        }else {
            sql.WHERE("crm.crm_type = 2");
        }
//        if(StringUtils.isNotBlank(finance.getCrmName())) {
//            sql.WHERE("a.crm_name like \"%\"#{finance.crmName}\"%\"");
//        }
       return sql.toString();
    }

    /**
     * 获取商机列表
     * @author 小啊刚
     * @date 2020/7/8
     */
    public String pageBusiness(CrmBusiness business, UserInfo user) {
        SQL sql = new SQL();
        sql.SELECT("a.*");
        sql.FROM("crm_business a");
        sql.INNER_JOIN("pub_customer crm on a.crm_id = crm.id");
        sql.WHERE("a.status = 0");
        if(business.getCrmId() != null) {
            sql.WHERE("a.crm_id = #{business.crmId}");
        }else {
            sql.WHERE("crm.crm_type != 0");
        }
        if(StringUtils.isNotBlank(business.getCrmName())) {
            sql.WHERE("a.crm_name like \"%\"#{business.crmName}\"%\"");
        }
        return sql.toString();
    }

    /**
     * 获取客户需求
     * @author 小啊刚
     * @date 2020/7/9
     */
    public String pageRequirement(CrmRequirement requirement, UserInfo user) {
        SQL sql = new SQL();
        sql.SELECT("a.*");
        sql.FROM("crm_requirement a");
        sql.INNER_JOIN("pub_customer crm on a.crm_id = crm.id");
        sql.WHERE("a.status = 0");
        if(requirement.getCrmId() != null) {
            sql.WHERE("a.crm_id = #{requirement.crmId}");
        }else {
            sql.WHERE("crm.crm_type = 1");
        }
        if(StringUtils.isNotBlank(requirement.getTitle())) {
            sql.WHERE("a.title like \"%\"#{requirement.title}\"%\"");
        }
        return sql.toString();
    }

    /**
     * 客户文档
     * @author 小啊刚
     * @date 2020/7/9
     */
    public String pageFilesList(CrmFiles files, UserInfo user) {
        SQL sql = new SQL();
        sql.SELECT("a.*");
        sql.FROM("crm_files a");
        sql.INNER_JOIN("pub_customer crm on a.crm_id = crm.id");
        sql.WHERE("a.status = 0");
        if(files.getCrmId() != null) {
            sql.WHERE("a.crm_id = #{files.crmId}");
        }else {
            sql.WHERE("crm.crm_type = 1");
        }
        return sql.toString();
    }

    /**
     * 客户跟近分析
     * @author 小啊刚
     * @date 2020-08-06
     */
    public String followAnalysis(CrmFollowAnalysis params) {
//        return new SQL() {
//            {
//                SELECT("crm.crm_name crmName, crm.office_id, crm.collar_user_id, crm.crm_type type, crm.last_touch_date lastTouchDate, crm.collar_user_name collarUserName, crm.industry, t.*, crm.company_id");
//                FROM("(select min(contact_date) firstTouchDate, crm_id, count(1) toucheTimes, to_days(now()) - to_days(max(contact_date)) noTouchDays from crm_touch group by crm_id) t");
//                LEFT_OUTER_JOIN("pub_customer crm on crm.id = t.crm_id");
//                WHERE("crm.status = 0");
//                if(StringUtils.isNotBlank(params.getCrmName())) {
//                    WHERE("crm.crm_name like \"%\"#{params.crmName}\"%\"");
//                }
//                if(params.getCrmType() != null) {
//                    WHERE("crm.crm_type = #{params.crmType}");
//                }
//                if(StringUtils.isNotBlank(params.getTouchBeginDate())) {
//                    WHERE("crm.last_touch_date >= #{params.touchBeginDate}");
//                }
//                if(StringUtils.isNotBlank(params.getTouchEndDate())) {
//                    WHERE("crm.last_touch_date <= #{params.touchEndDate}");
//                }
//                if(StringUtils.isNotBlank(params.getUserId())) {
//                    WHERE("t.sales_man_id = #{params.userId}");
//                }
//                if(StringUtils.isNotBlank(params.getTouchStatus())) {
//                    if(params.getTouchStatus().equals("0")) {
//                        WHERE("t.firstTouchDate is null");
//                    }else {
//                        WHERE("t.firstTouchDate is not null");
//                    }
//                }
//            }
//        }.toString();
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT t.*, crm.crm_name, crm.crm_type type, crm.last_touch_date lastTouchDate, crm.collar_user_name collarUserName, crm.industry FROM ");
        sql.append("(SELECT min( contact_date ) firstTouchDate, crm_id, count( 1 ) toucheTimes, to_days(now()) - to_days(max( contact_date )) noTouchDays FROM ");
        sql.append("(select t.*,e.office_id from crm_touch t left join sys_user e on t.sales_man_id = e.id");
        sql.append(") touch ");
        sql.append("GROUP BY touch.crm_id)t LEFT JOIN pub_customer crm ON crm.id = t.crm_id ");
        return sql.toString();
    }
}
